﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using MySql.Data.MySqlClient;

namespace WorkFlow
{
    public class SqlHelper
    {
        public SqlHelper()
        {
        }

        //数据库连接串设置
        public static string connString = "Server=localhost;Database=workflow;Uid=root;Pwd=Dw12345678.;";
        public MySqlConnection myConn = new MySqlConnection(connString);

        /// <summary>
        /// 打开数据库连接.
        /// </summary>
        public void Open()
        {
            try
            {
                if (myConn == null)
                {
                    //如果myConn没有实例化，则新建个对象
                    //myConn = new MySqlConnection(connString);
                    //myConn.Open();
                    LogHelper.WriteLog("myConn没有实例化");
                }
                else if (myConn.State == System.Data.ConnectionState.Closed)
                {
                    //如果myConn关闭，则打开
                    myConn.Open();
                }
                else if (myConn.State == System.Data.ConnectionState.Broken)
                {
                    //如果myConn中断，则关闭后，再打开
                    myConn.Close();
                    myConn.Open();
                }
                else if (myConn.State == System.Data.ConnectionState.Open)
                {
                    return;
                }
                else
                {
                    myConn.Open();
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
            }
        }

        /// <summary>
        /// 为MySqlCommand.CommandText赋值
        /// </summary>
        /// <param name="cmd">MySqlCommand对象</param>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">sql语句参数</param>
        public void setSqlCommand(MySqlCommand cmd, string sqlString, MySqlParameter[] parameters)
        {
            if (parameters != null)
            {
                foreach (MySqlParameter parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        /// <summary>
        /// 得到数据集
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="parameters">sql语句参数</param>
        /// <returns>数据集</returns>
        public DataSet GetDataSet(string sqlString, MySqlParameter[] parameters)
        {
            DataSet ds = new DataSet();
            try
            {
                //打开数据库
                Open();

                using (MySqlCommand cmd = new MySqlCommand(sqlString, myConn))
                {
                    setSqlCommand(cmd, sqlString, parameters);
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                    //把适配器中的查询结果填充到ds中表名为tableName
                    da.Fill(ds);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
            }
            finally
            {
                //关闭数据库
                //myConn.Close();
            }
            return ds;
        }

        /// <summary>
        /// 得到数据集
        /// </summary>
        /// <param name="tableName">DataSet中的表名</param>
        /// <param name="sqlString">sql语句</param>
        /// <param name="parameters">sql语句参数</param>
        /// <returns>数据集</returns>
        public DataSet GetDataSet(string tableName, string sqlString, MySqlParameter[] parameters)
        {
            DataSet ds = new DataSet();
            try
            {
                //打开数据库
                Open();

                //创建适配器对象
                using (MySqlCommand cmd = new MySqlCommand(sqlString, myConn))
                {
                    setSqlCommand(cmd, sqlString, parameters);
                    MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
                    //把适配器中的查询结果填充到ds中表名为tableName
                    sda.Fill(ds);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
            }
            finally
            {
                //关闭数据库
                //myConn.Close();
            }
            return ds;
        }

        /// <summary>
        /// 受影响的行数，执行成功返回1，失败返回0
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns>0或1</returns>
        public int GetNonQuery(string sqlString, MySqlParameter[] parameters)
        {
            //val用来返回值
            int val = 0;

            try
            {
                //打开数据库
                Open();
                //声明要执行的对象
                using (MySqlCommand cmd = new MySqlCommand(sqlString, myConn))
                {
                    setSqlCommand(cmd, sqlString, parameters);
                    val = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
            }
            finally
            {
                //关闭数据库
                //myConn.Close();
            }
            return val;
        }



        /// <summary>
        /// 受影响的行数，执行成功返回1，失败返回0
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns>0或1</returns>
        public bool GetNonQuery1(string sqlString, MySqlParameter[] parameters)
        {
            //val用来返回值
            bool val = false;
            try
            {
                //打开数据库
                Open();
                //声明要执行的对象
                using (MySqlCommand cmd = new MySqlCommand(sqlString, myConn))
                {
                    setSqlCommand(cmd, sqlString, parameters);
                    cmd.ExecuteNonQuery();
                    val = true;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
            }
            finally
            {
                //关闭数据库
                //myConn.Close();
            }
            return val;
        }

        /// <summary>
        /// 得到记录行数，执行成功返回1，失败返回0
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns>0或一个整数</returns>
        public int GetScalar(string sqlString, MySqlParameter[] parameters)
        {
            //val用来返回值
            int val = 0;
            try
            {
                //打开数据库
                Open();
                //声明要执行的对象
                using (MySqlCommand cmd = new MySqlCommand(sqlString, myConn))
                {
                    setSqlCommand(cmd, sqlString, parameters);
                    //试着执行代码，如果出错则跳转到catch
                    val = (int)cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
            }
            finally
            {
                //关闭数据库
                //myConn.Close();
            }
            return val;
        }

        /// <summary>
        /// 得到记录行数，执行成功返回1，失败返回0
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns>0或一个整数</returns>
        public int ExecuteNonQuery(string sqlString, MySqlParameter[] parameters, out string message)
        {
            message = "";
            //val用来返回值
            int val = 0;

            try
            {  //打开数据库
                Open();
                //声明要执行的对象
                using (MySqlCommand cmd = new MySqlCommand(sqlString, myConn))
                {
                    setSqlCommand(cmd, sqlString, parameters);
                    //试着执行代码，如果出错则跳转到catch
                    val = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                val = -1;
                message = ex.ToString();
                LogHelper.WriteLog(ex);
            }
            finally
            {
                //关闭数据库
                //myConn.Close();
            }
            return val;
        }


        //定义方法为读取数据库的内容

        public DataSet ExecuteDataSet(String tablename, String cmdText)
        {
            DataSet ds = new DataSet();
            try
            {
                Open();
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmdText, myConn))
                {
                    da.Fill(ds, tablename);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
            }
            finally
            {
                //myConn.Close();
            }
            return ds;
        }

        public int ExecuteNonQueryAndID(String cmdText, out string ID, out string message)
        {
            ID = "";
            message = "";
            //此方法执行sql，如果执行成功返回1，失败返回0
            int val = -1;

            //打开数据库
            try
            {
                Open();
                using (MySqlCommand cmd = new MySqlCommand(cmdText, myConn))
                {
                    LogHelper.WriteLog("数据库执行Sql语句：\r\n" + cmdText);
                    val = cmd.ExecuteNonQuery();
                    ID = cmd.LastInsertedId.ToString();
                }
            }
            catch (Exception ex)
            {
                message = ex.Message;
                LogHelper.WriteLog("数据库失败\r\nSql语句：\r\n" + cmdText);
                LogHelper.WriteLog(ex);
            }
            finally
            {
                myConn.Close();
            }
            return val;
        }
        public int ExecuteNonQuery(String cmdText, out string message)
        {
            message = "";
            //此方法执行sql，如果执行成功返回1，失败返回0
            int val = -1;

            //打开数据库
            try
            {
                Open();
                using (MySqlCommand cmd = new MySqlCommand(cmdText, myConn))
                {
                    LogHelper.WriteLog("数据库执行Sql语句：\r\n" + cmdText);
                    val = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                message = ex.Message;
                LogHelper.WriteLog("数据库失败\r\nSql语句：\r\n" + cmdText);
                LogHelper.WriteLog(ex);
            }
            finally
            {
                myConn.Close();
            }
            return val;
        }
        public static string RemoveSpecialChar(string sql)
        {
            sql = sql.Replace("'","");
            sql = sql.Replace("\\", "");
            sql = sql.Replace(",","");
            sql = sql.Replace(";","");
            return sql;
        }
        public int SaveDataTable(string cmdText, DataTable dataTable, out string message)
        {
            message = "";
            //此方法执行sql，如果执行成功返回1，失败返回0
            int val = -1;

            //打开数据库
            try
            {
                Open();
                using (MySqlDataAdapter sda = new MySqlDataAdapter(cmdText, myConn))
                {
                    LogHelper.WriteLog("数据库执行Sql语句：\r\n" + cmdText);
                    MySqlCommandBuilder scb = new MySqlCommandBuilder(sda);
                    val = sda.Update(dataTable);
                }
            }
            catch (Exception ex)
            {
                message = ex.Message;
                LogHelper.WriteLog("数据库失败\r\nSql语句：\r\n" + cmdText);
                LogHelper.WriteLog(ex);
            }
            finally
            {
                myConn.Close();
            }
            return val;
        }

        /// <summary>
        /// 批量处理sql语句
        /// </summary>
        /// <param name="cmdTextList"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(List<string> cmdTextList, out string message)
        {
            message = "";
            //此方法执行sql，如果执行成功返回操作数量，失败返回-1
            int val = 0;
            //打开数据库
            Open();
            // 回滚实例
            MySqlTransaction sqlTran = myConn.BeginTransaction();
            try
            {
                for (int i = 0; i < cmdTextList.Count; i++)
                {
                    string cmdText = cmdTextList[i];
                    LogHelper.WriteLog("数据库执行Sql语句：\r\n" + cmdText);
                    using (MySqlCommand cmd = new MySqlCommand(cmdText, myConn))
                    {
                        cmd.Connection = myConn;
                        cmd.Transaction = sqlTran;
                        int result = cmd.ExecuteNonQuery();
                        val += result;
                    }
                }
                sqlTran.Commit();
            }
            catch (Exception ex)
            {
                // 错误回滚
                sqlTran.Rollback();
                val = -1;
                message = ex.Message;
                LogHelper.WriteLog(ex);
            }
            finally
            {
                //myConn.Close();
            }
            return val;
        }

        /// <summary>
        /// 批量处理sql语句
        /// </summary>
        /// <param name="cmdTextDict">执行代码和对应错误信息</param>
        /// <returns></returns>
        public int ExecuteNonQuery(Dictionary<string, string> cmdTextDict, out string message)
        {
            message = "";
            //此方法执行sql，如果执行成功返回操作数量，失败返回-1
            int val = 0;
            Open();
            // 回滚实例
            MySqlTransaction sqlTran = myConn.BeginTransaction();
            //打开数据库
            try
            {
                foreach (string key in cmdTextDict.Keys)
                {
                    string cmdText = key;
                    LogHelper.WriteLog("数据库执行Sql语句：\r\n" + cmdText);
                    using (MySqlCommand cmd = new MySqlCommand(cmdText, myConn))
                    {
                        cmd.Connection = myConn;
                        cmd.Transaction = sqlTran;
                        int result = cmd.ExecuteNonQuery();
                        if (result == 0)
                            message += cmdTextDict[key] + "☆";
                        val += result;
                    }
                }
                sqlTran.Commit();
            }
            catch (Exception ex)
            {
                // 错误回滚
                sqlTran.Rollback();
                val = -1;
                message = ex.Message;
                string sqlBudder = "";
                foreach (string str in cmdTextDict.Keys)
                {
                    sqlBudder += str + "\r\n";
                }
                LogHelper.WriteLog("数据库失败\r\nSql语句：\r\n" + sqlBudder);
                LogHelper.WriteLog(ex);
            }
            finally
            {
                //myConn.Close();
            }
            return val;
        }

        /// <summary>
        /// 批量处理sql语句（库存调拨使用）
        /// </summary>
        /// <param name="cmdTextDict">执行代码和对应错误信息</param>
        /// <returns></returns>
        public int ExecuteNonQueryForAllot(Dictionary<string, string> cmdTextDict, out string message)
        {
            message = "";
            //此方法执行sql，如果执行成功返回操作数量，失败返回-1
            int val = 0;
            Open();
            // 回滚实例
            MySqlTransaction sqlTran = myConn.BeginTransaction();
            //打开数据库
            try
            {
                foreach (string key in cmdTextDict.Keys)
                {
                    string cmdText = key;
                    LogHelper.WriteLog("数据库执行Sql语句：\r\n" + cmdText);
                    using (MySqlCommand cmd = new MySqlCommand(cmdText, myConn))
                    {
                        cmd.Connection = myConn;
                        cmd.Transaction = sqlTran;
                        int result = cmd.ExecuteNonQuery();
                        if (result == 0)
                        {
                            string otherStr = cmdTextDict[key];
                            string[] oprationStrs = otherStr.Split('☆');
                            int oprationState = int.Parse(oprationStrs[0]);
                            if (oprationState == 0)
                                message += cmdTextDict[key] + "☆";
                            else
                            {
                                // 当入库库存中不存在该物品时，自动向该仓库添加产品并且库存中增加数量
                                string[] propertyList = otherStr.Substring(2).Replace("【", "").Replace("】", "").Split('☆');
                                string productid = propertyList[0].Split('|')[1];
                                string allotamount = propertyList[1].Split('|')[1];
                                string areaid = propertyList[2].Split('|')[1];
                                // 查询被调拨的产品信息
                                using (MySqlCommand cmdProduct = myConn.CreateCommand())
                                {
                                    cmdProduct.Transaction = sqlTran;

                                    #region 库区添加产品

                                    //    MySqlDataAdapter da = new MySqlDataAdapter(cmdProduct);
                                    //    DataSet ds = new DataSet();
                                    //    da.Fill(ds, "Dpot_product");
                                    //    DataTable dt = ds.Tables[0];
                                    //    if (dt.Rows.Count != 1)
                                    //    {
                                    //        message += "调拨产品不唯一";
                                    //        return -1;
                                    //    }
                                    //    string insertProductSql = "insert into Dpot_product";
                                    //    string nameStr = "(";
                                    //    string valueStr = " values(";
                                    //    for (int i = 0; i < dt.Columns.Count; i++)
                                    //    {
                                    //        string columnName = dt.Columns[i].ColumnName;
                                    //        nameStr += columnName + ",";
                                    //        // 生成一个新的产品ID
                                    //        if (columnName == "id")
                                    //        {
                                    //            valueStr += "'" + Guid.NewGuid() + "',";
                                    //        }
                                    //        else
                                    //        {
                                    //            valueStr += "'" + dt.Rows[0][columnName] + "',";
                                    //        }
                                    //        // 库区要设置成调入库区
                                    //        if (columnName == "areaid")
                                    //        {
                                    //            valueStr += "'" + areaid + "',";
                                    //        }
                                    //        else
                                    //        {
                                    //            valueStr += "'" + dt.Rows[0][columnName] + "',";
                                    //        }
                                    //    }
                                    //    nameStr = nameStr.Substring(0, nameStr.Length - 1);
                                    //    nameStr += ") ";
                                    //    valueStr = valueStr.Substring(0, valueStr.Length - 1);
                                    //    valueStr += ") ";
                                    //    string sqlStr = insertProductSql + nameStr + valueStr;
                                    //    cmdProduct.CommandText = sqlStr;
                                    //    result = cmdProduct.ExecuteNonQuery();
                                    //    if (result == 0)
                                    //    {
                                    //        message += "插入产品失败：产品名称【" + dt.Rows[0]["name"] + "】 产品编码【" + dt.Rows[0]["code" + "】"];
                                    //        return -1;
                                    //    }
                                    #endregion

                                    #region 向调入库存中插入数量
                                    string insertStockSql = "insert into Dpot_stock(id,areaid,productid,amount,remark,createtime,enable)"
                                        + " values('" + Guid.NewGuid() + "','" + areaid + "','" + productid + "','" + allotamount + "','" + "调拨自动生成记录" + "','"
                                        + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',1)";
                                    cmdProduct.CommandText = insertStockSql;
                                    result = cmdProduct.ExecuteNonQuery();
                                    if (result == 0)
                                    {
                                        message += "调入库存中插入记录失败：productid【" + productid + "】 areaid【" + areaid + "】";
                                        return -1;
                                    }
                                    #endregion
                                }
                            }
                        }
                        val += result;
                    }

                }
                sqlTran.Commit();
            }
            catch (Exception ex)
            {
                // 错误回滚
                sqlTran.Rollback();
                val = -1;
                message = ex.Message;
                LogHelper.WriteLog(ex);
            }
            finally
            {
                //myConn.Close();
            }
            return val;
        }

        public DataSet myDataSet(string cmdText)
        {

            DataSet myDataSet = new DataSet();
            try
            {
                //打开数据库
                Open();
                using (MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(cmdText, myConn))
                {
                    LogHelper.WriteLog("数据库执行Sql语句：\r\n" + cmdText);
                    myDataAdapter.Fill(myDataSet);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("数据库失败\r\nSql语句：\r\n" + cmdText);
                LogHelper.WriteLog("堆栈信息：", ex);
            }
            finally
            {
                myConn.Close();
            }
            return myDataSet;
        }


        //用于返回记录行数
        public int ExecuteScalar(string cmdText)
        {
            //此方法执行sql，如果执行成功返回1，失败返回0
            int val = 0;//val用来返回值

            try
            {
                //打开数据库
                Open();
                //声明要执行的对象
                using (MySqlCommand cmd = new MySqlCommand(cmdText, myConn))
                {
                    LogHelper.WriteLog("数据库执行Sql语句：\r\n" + cmdText);
                    //试着执行代码，如果出错则跳转到catch
                    val = (int)cmd.ExecuteScalar();//执行sql语句
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("数据库失败\r\nSql语句：\r\n" + cmdText);
                LogHelper.WriteLog("堆栈信息：", ex);
            }
            finally
            {
                //myConn.Close();
            }
            return val;
        }



        #region  权限进行分页

        public int GetCount(string tablename, string where)
        {

            string _sql = "select count(*) from " + tablename + " where 1 = 1 " + where + "";

            int count = 0;
            try
            {
                count = int.Parse(myDataSet(_sql).Tables[0].Rows[0][0].ToString());
            }
            catch (Exception ex)
            {

                LogHelper.WriteLog(ex);
            }
            return count;

        }

        public DataSet GetPermissionsPager(string sort, string order, int currentPage, int pageSize, string where, string tablename)
        {
            string _sql = @" select  *
                                from (select  *,Row_number() over(order by " + sort + " " + order + ") as IDRank from " + tablename + " WHERE 1 = 1 " + where + ") as IDWithRowNumber where IDRank>=(" + currentPage + "-1)*" + pageSize + "+1 and IDRank<=" + currentPage + "*" + pageSize + "";

            return myDataSet(_sql);
        }

        #endregion
    }
}